﻿/*----------------------------------------------------
 * Author : Tharinduni Udugamasuriya
 * FinCon dll for Handeling DB 
 * Writer : Handeling DB Commands - inner
 ---------------------------------------------------*/

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace DSPL.DataAccess
{
    public class Writer : QueryBuilder
    {
        SqlCommand SqlCmd;


        public object Excecute(SpCaller spCaller, ref SqlConnection SqlConn)
        {
            try
            {
                if (SqlConn.State == System.Data.ConnectionState.Open)
                {

                    SqlCmd = new SqlCommand(spCaller.SpName, SqlConn);
                    SqlCmd.CommandType = CommandType.StoredProcedure;

                    foreach (ParameterWithValue item in spCaller.SpParamValues)
                    {
                        SqlCmd.Parameters.Add(item.SpParameter);
                        item.SpParameter.IsNullable = item.SpParameter.Value == DBNull.Value;
                    }

                    return SqlCmd.ExecuteNonQuery();

                }
                else
                {
                    throw new Exception("Database Connection is not open");
                }
            }
            catch (SqlException ex)
            {

                throw ex;
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }

        public object Excecute(Query query, ref SqlConnection SqlConn, bool isParameterized = false)
        {
            try
            {
                if (SqlConn.State == System.Data.ConnectionState.Open)
                {
                    SqlCmd = new SqlCommand();

                    if (isParameterized)
                        SqlCmd = (SqlCommand)IUDQueryBuilder(query, ref SqlCmd);
                    else
                        SqlCmd.CommandText = IUDQueryBuilder(query);

                    SqlCmd.Connection = SqlConn;
                    SqlCmd.CommandType = CommandType.Text;
                    return SqlCmd.ExecuteNonQuery();
                }
                else
                {
                    throw new Exception("Database Connection is not open");
                }
            }
            catch (SqlException ex)
            {

                throw ex;
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }

        public object Excecute(SpCaller spCaller, string retPara, ref SqlConnection SqlConn)
        {
            try
            {
                if (string.IsNullOrEmpty(retPara))
                {
                    return Excecute(spCaller, ref SqlConn);
                }
                else
                {
                    if (SqlConn.State == System.Data.ConnectionState.Open)
                    {

                        SqlCmd = new SqlCommand(spCaller.SpName, SqlConn);
                        SqlCmd.CommandType = CommandType.StoredProcedure;

                        foreach (ParameterWithValue item in spCaller.SpParamValues)
                        {
                            SqlCmd.Parameters.Add(item.SpParameter);
                            item.SpParameter.IsNullable = item.SpParameter.Value == DBNull.Value;
                        }

                        SqlCmd.ExecuteNonQuery();
                        return SqlCmd.Parameters[retPara].Value;

                    }
                    else
                    {
                        throw new Exception("Database Connection is not open");
                    }
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /*public object Excecute(FinQuery query, string retPara, ref SqlConnection SqlConn)
        {
            try
            {
                if (string.IsNullOrEmpty(retPara))
                {
                    return Excecute(query, ref SqlConn);
                }
                else
                {
                    if (SqlConn.State == System.Data.ConnectionState.Open)
                    {

                        SqlCmd = new SqlCommand(QueryBuilder(query), SqlConn);
                        SqlCmd.CommandType = CommandType.Text;
                        return SqlCmd.ExecuteNonQuery();

                    }
                    else
                    {
                        throw new Exception("Database Connection is not open");
                    }
                }
            }
            catch (Exception)
            {
                throw;
            }
        }*/

        public object Excecute(SpCaller spCaller, string retPara, bool timeoutOff, ref SqlConnection SqlConn)
        {
            try
            {

                if (string.IsNullOrEmpty(retPara))
                {
                    return Excecute(spCaller, ref SqlConn);
                }
                else if (!timeoutOff)
                {
                    return Excecute(spCaller, retPara, ref SqlConn);
                }
                else
                {
                    if (SqlConn.State == System.Data.ConnectionState.Open)
                    {

                        SqlCmd = new SqlCommand(spCaller.SpName, SqlConn);
                        SqlCmd.CommandType = CommandType.StoredProcedure;
                        SqlCmd.CommandTimeout = 0;
                        foreach (ParameterWithValue item in spCaller.SpParamValues)
                        {
                            SqlCmd.Parameters.Add(item.SpParameter);
                            item.SpParameter.IsNullable = item.SpParameter.Value == DBNull.Value;
                        }

                        SqlCmd.ExecuteNonQuery();
                        return SqlCmd.Parameters[retPara].Value;

                    }
                    else
                    {
                        throw new Exception("Database Connection is not open");
                    }
                }
            }
            catch (SqlException ex)
            {

                throw ex;
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }

        public object Excecute(SpCaller spCaller, ref SqlTransaction sqlTran, ref SqlConnection SqlConn)
        {
            try
            {
                if (sqlTran == null)
                {
                    sqlTran = SqlConn.BeginTransaction();
                }
                if (SqlConn.State == System.Data.ConnectionState.Open)
                {


                    SqlCmd = new SqlCommand(spCaller.SpName, SqlConn);
                    SqlCmd.CommandTimeout = 0;
                    SqlCmd.CommandType = CommandType.StoredProcedure;
                    SqlCmd.Transaction = sqlTran;
                    foreach (ParameterWithValue item in spCaller.SpParamValues)
                    {
                        SqlCmd.Parameters.Add(item.SpParameter);
                        item.SpParameter.IsNullable = item.SpParameter.Value == DBNull.Value;
                    }

                    return SqlCmd.ExecuteNonQuery();

                }
                else
                {
                    throw new Exception("Database Connection is not open");
                }
            }
            catch (SqlException ex)
            {

                throw ex;
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }

        public object Excecute(Query query, ref SqlTransaction sqlTran, ref SqlConnection SqlConn, bool isParameterized = false)
        {
            try
            {
                if (sqlTran == null)
                {
                    sqlTran = SqlConn.BeginTransaction();
                }
                if (SqlConn.State == System.Data.ConnectionState.Open)
                {
                    SqlCmd = new SqlCommand();

                    if (isParameterized)
                        SqlCmd = (SqlCommand)IUDQueryBuilder(query, ref SqlCmd);
                    else
                        SqlCmd.CommandText = IUDQueryBuilder(query);

                    SqlCmd.Connection = SqlConn;
                    SqlCmd.CommandType = CommandType.Text;
                    SqlCmd.CommandTimeout = 0;
                    SqlCmd.Transaction = sqlTran;
                    return SqlCmd.ExecuteNonQuery();

                }
                else
                {
                    throw new Exception("Database Connection is not open");
                }
            }
            catch (SqlException ex)
            {

                throw ex;
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }

        public object Excecute(SpCaller spCaller, string retPara, ref SqlTransaction sqlTran, ref SqlConnection SqlConn)
        {
            try
            {
                if (sqlTran == null)
                {
                    sqlTran = SqlConn.BeginTransaction();
                }


                if (string.IsNullOrEmpty(retPara))
                {
                    return Excecute(spCaller, ref sqlTran, ref SqlConn);
                }
                else
                {
                    if (SqlConn.State == System.Data.ConnectionState.Open)
                    {


                        SqlCmd = new SqlCommand(spCaller.SpName, SqlConn);
                        SqlCmd.CommandType = CommandType.StoredProcedure;
                        SqlCmd.Transaction = sqlTran;
                        foreach (ParameterWithValue item in spCaller.SpParamValues)
                        {
                            SqlCmd.Parameters.Add(item.SpParameter);
                            item.SpParameter.IsNullable = item.SpParameter.Value == DBNull.Value;
                        }

                        SqlCmd.ExecuteNonQuery();
                        return SqlCmd.Parameters[retPara].Value;

                    }
                    else
                    {
                        throw new Exception("Database Connection is not open");
                    }
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

    }
}
